package com.anolesoft.yr.mbus.prod.model.sql;

public class ProdProductionSQL {

    //新增时查询产品编号是否已经存在
    public static final String QUERY_PROD_PRODUCTION_BY_CODE = "select count(*) as count from prod_production a where a.del_flag=0 and a.prod_code=#{prodCode}";

    //新增时查询产品名称是否已经存在
    public static final String QUERY_PROD_PRODUCTION_BY_NAME = "select count(*) as count from prod_production a where a.del_flag=0 and a.prod_name=#{prodName}";

    //新增产品信息表
    public static final String SAVE_PROD_PRODUCTION = "insert into prod_production(objuid,fac_code,prod_type_uid,prod_code,prod_name,remark,c_user_uid,c_time,del_flag) values(#{objuid},#{facCode},#{prodTypeUid},#{prodCode},#{prodName},#{remark},#{ccUserUid},#{ccTime},#{delFlag})";

    //根据主键查询产品唯一数据
    public static final String QUERY_PROD_PRODUCTION_BY_UID2 = "select * from prod_production a where a.objuid=#{objuid}";

    //新增时查询产品编号是否已经存在（不包含本身）
    public static final String QUERY2_PROD_PRODUCTION_BY_CODE = "select count(*) as count from prod_production a where a.del_flag=0 and a.prod_type_uid=#{prodTypeUid} and a.prod_code=#{prodCode} and a.prod_code<>#{prodCode2}";

    //新增时查询产品名称是否已经存在（不包含本身）
    public static final String QUERY2_PROD_PRODUCTION_BY_NAME = "select count(*) as count from prod_production a where a.del_flag=0 and a.prod_type_uid=#{prodTypeUid} and a.prod_name=#{prodName} and a.prod_name<>#{prodName2}";

    //修改产品信息表
    public static final String UPDATE_PROD_PRODUCTION = "update prod_production a set a.fac_code=#{facCode},a.prod_type_uid=#{prodTypeUid},a.prod_code=#{prodCode},a.prod_name=#{prodName},a.remark=#{remark},a.m_user_uid=#{mmUserUid},a.m_time=#{mmTime} where a.objuid=#{objuid}";

    //删除产品信息
    public static final String DELETE_PROD_PRODUCTION = "update prod_production a set a.del_flag=1 where a.objuid=#{objuid}";

    //根据主键查询产品唯一数据
    public static final String QUERY_PROD_PRODUCTION_BY_UID = "select a.objuid,b.objuid as prod_type_uid,b.type_name,a.prod_code,a.prod_name,a.remark from prod_production a left join prod_type b on b.objuid=a.prod_type_uid where a.del_flag=0 and a.objuid=#{uid}";

    //根据条件查询产品数据条数
    public static final String QUERY_PROD_PRODUCTION_ALL_COUNT = "<script> select count(*) as count from prod_production a left join prod_type b on b.objuid=a.prod_type_uid left join fac_employee c on c.objuid=a.c_user_uid where a.del_flag=0 <if test='typeName!=null'> and upper(b.objuid) like upper(concat('%',#{typeName},'%')) </if> <if test='prodName!=null'> and upper(a.prod_name) like upper(concat('%',#{prodName},'%')) </if> </script>";

    //根据条件查询产品所有数据
    public static final String QUERY_PROD_PRODUCTION_ALL = "<script> select a.objuid,b.type_name,a.prod_code,a.prod_name,a.remark,c.emp_name as c_user_uid,a.c_time from prod_production a left join prod_type b on b.objuid=a.prod_type_uid left join fac_employee c on c.objuid=a.c_user_uid where a.del_flag=0 <if test='typeName!=null'> and upper(b.objuid) like upper(concat('%',#{typeName},'%')) </if> <if test='prodName!=null'> and upper(a.prod_name) like upper(concat('%',#{prodName},'%')) </if> order by b.type_name asc, a.prod_name asc limit #{page},#{limit} </script>";

    //查询产品名称
    public static final String QUERY_PROD_PRODUCTION_PROD_NAME = "select a.objuid,a.prod_name from prod_production a where a.del_flag=0 order by a.prod_name asc";

}
